USE SERVICIOS
GO
--SP_HELPTEXT [SER_S_T_Juridica_Listado]
--SP_HELPTEXT [SER_D_DetalleConvocatoriaExpediente]
--SP_HELPTEXT [SER_IU_DetalleConvocatoriaExpediente]
--SP_HELPTEXT [SER_U_DetalleConvocatoriaExpediente_Adjudicar]
GO
IF OBJECT_ID(N'dbo.[SER_S_T_Juridica_Listado]',N'P') IS NOT NULL
   DROP PROCEDURE [SER_S_T_Juridica_Listado];
GO
IF OBJECT_ID(N'dbo.[SER_D_DetalleConvocatoriaExpediente]',N'P') IS NOT NULL
   DROP PROCEDURE [SER_D_DetalleConvocatoriaExpediente];
GO
IF OBJECT_ID(N'dbo.[SER_IU_DetalleConvocatoriaExpediente]',N'P') IS NOT NULL
   DROP PROCEDURE [SER_IU_DetalleConvocatoriaExpediente];
GO
IF OBJECT_ID(N'dbo.[SER_U_DetalleConvocatoriaExpediente_Adjudicar]',N'P') IS NOT NULL
   DROP PROCEDURE [SER_U_DetalleConvocatoriaExpediente_Adjudicar];
GO
/**********************************************************
CONCESIONARIOS
	Creado por : MGH
	Modificado por : NA
	Fechas		: 23/02/2011 - SP Inicial
	Objetivo   	: Selecciona personas juridicas para combos
***********************************************************/
CREATE PROCEDURE [SER_S_T_Juridica_Listado]   
AS
BEGIN
SET NOCOUNT ON

	SELECT  0 AS CODIGO, '0' AS RUC, '(Seleccione...)' AS DESCRIPCION
	UNION ALL
	SELECT	CO_JURIDICO AS CODIGO,
			NO_RUC AS RUC,
			NO_NOMBRE1 AS DESCRIPCION
	FROM T_Juridica JUR WITH(NOLOCK)
	INNER JOIN T_Persona PER WITH(NOLOCK) ON JUR.CO_PERSONA = PER.CO_PERSONA
	ORDER BY 1

SET NOCOUNT OFF
END
GO

/**********************************************************
CONCESIONARIOS
	Creado por : MGH
	Modificado por : NA
	Fechas		: 23/02/2011 - SP Inicial
	Objetivo   	: Elimina expedientes por cada detalle de convocatoria
***********************************************************/
CREATE PROCEDURE [SER_D_DetalleConvocatoriaExpediente]   
	@Co_Convocatoria INT
AS
BEGIN
SET NOCOUNT ON

	DELETE FROM [T_SER_ESI_Expediente] WHERE Co_Convocatoria=@Co_Convocatoria

	DELETE FROM [T_SER_ESI_DetalleConvocatoria] WHERE Co_Convocatoria=@Co_Convocatoria

SET NOCOUNT OFF
END
GO
/**********************************************************
CONCESIONARIOS
	Creado por : MGH
	Modificado por : NA
	Fechas		: 23/02/2011 - SP Inicial
	Objetivo   	: Inserta expedientes por cada detalle de convocatoria
***********************************************************/
CREATE PROCEDURE [SER_IU_DetalleConvocatoriaExpediente]   
	@Co_Convocatoria INT,
	@Co_Juridico INT,
	@Co_Expediente INT,
	@Fl_Ganador	BIT,
	@Tx_Observacion VARCHAR(250)
AS
BEGIN
SET NOCOUNT ON

	IF LEN(LTRIM(RTRIM(@Tx_Observacion)))=0
		SET @Tx_Observacion = NULL

	UPDATE [T_SER_ESI_DetalleConvocatoria]
		SET Fl_Ganador = @Fl_Ganador
	WHERE Co_Convocatoria=@Co_Convocatoria AND Co_Juridico=@Co_Juridico

	IF @@ROWCOUNT =0
	BEGIN
		INSERT INTO [T_SER_ESI_DetalleConvocatoria] VALUES (@Co_Convocatoria, @Co_Juridico, @Fl_Ganador)
	END

	UPDATE [T_SER_ESI_Expediente]
		SET Co_Convocatoria=@Co_Convocatoria,
			Co_Juridico=@Co_Juridico,
			Fe_Recepcion = GETDATE(),
			Tx_Observacion = @Tx_Observacion
	WHERE Co_Expediente=@Co_Expediente

	IF @@ROWCOUNT =0
	BEGIN
		INSERT INTO [T_SER_ESI_Expediente] VALUES (@Co_Convocatoria, @Co_Juridico, GETDATE(), @Tx_Observacion)
	END
SET NOCOUNT OFF
END
GO
/**********************************************************
CONCESIONARIOS
	Creado por : MGH
	Modificado por : NA
	Fechas		: 23/02/2011 - SP Inicial
	Objetivo   	: Modifica flag ganador
***********************************************************/
CREATE PROCEDURE [SER_U_DetalleConvocatoriaExpediente_Adjudicar]   
	@Co_Convocatoria INT,
	@Co_Juridico INT
AS
BEGIN
SET NOCOUNT ON

	UPDATE [T_SER_ESI_DetalleConvocatoria]
		SET Fl_Ganador = 1
	WHERE Co_Convocatoria=@Co_Convocatoria AND Co_Juridico=@Co_Juridico

	SELECT @@ROWCOUNT AS [ROWCOUNT]

SET NOCOUNT OFF
END
GO
